#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
@Project ：zhcx-service-test 
@File    ：query_sql_method.py
@Author  ：关依林
@Date    ：2023/9/9 21:41 
'''

import datetime
from testcases.ProductGrading_Strategy.AmazonListing.listing_grading_public_way import *


def getSliceOrderSourceSkuData(id, cursor, data):

    sql = f"SELECT msku FROM t_amazon_listing_{id} WHERE id IN {data}"
    res = cursor.execute_sql_many(sql)
    orderSourceSkuRes = tuple([i["msku"] for i in res])
    return orderSourceSkuRes

def getOrderOtherListingSkuData(cursor, id, batchData):
    sql = f"SELECT source_id, sku, msku, country FROM " \
          f"t_amazon_listing_{id} WHERE id IN {batchData}"
    res = cursor.execute_sql_many(sql)
    return res

def getSliceMSkuData(id, cursor, data):

    sql = f"SELECT msku FROM t_amazon_listing_{id} WHERE customer_id = 1 AND is_delete = 0 AND msku IN {data}"
    res = cursor.execute_sql_many(sql)
    orderSourceSkuRes = tuple([i["msku"] for i in res])
    return orderSourceSkuRes

def getFbaListingData(orderSourceId, cursor, sliceOrderSourceSkuData):

    sql = f"SELECT t1.source_id, t1.msku, t1.receiving_time " \
          f"FROM t_amz_fba_shipment_item t1 " \
          f"JOIN (SELECT msku, MAX(id) AS max_id " \
          f"FROM t_amz_fba_shipment_item " \
          f"WHERE source_id = {orderSourceId} AND msku IN {sliceOrderSourceSkuData} " \
          f"GROUP BY msku) t2 ON t1.msku = t2.msku AND t1.id = t2.max_id " \
          f"WHERE t1.source_id = {orderSourceId} AND t1.msku IN {sliceOrderSourceSkuData};"
    res = cursor.execute_sql_many(sql)
    return res

def getWarehouseListingData(cursor, orderSourceSkuData, queryFiled):

    filed = ','.join(queryFiled)
    sql = f"SELECT t1.id,t1.source_id,t1.msku,{filed} " \
          f"FROM t_amz_fba_virtual_inventory t1 JOIN t_amz_fba_replenishment_suggestions t2 " \
          f"ON t1.id = t2.id AND t1.customer_id = t2.customer_id " \
          f"WHERE t1.msku IN {orderSourceSkuData}"
    res = cursor.execute_sql_many(sql)
    return res

def getWarehouseFbaListingData(cursor, orderSourceSkuData, queryFiled, templateRule=None):

    filed = ','.join(queryFiled)
    sql = f"SELECT t1.id,t1.source_id,t1.msku,{filed} " \
          f"FROM t_amz_fba_virtual_inventory t1 JOIN t_amz_fba_replenishment_suggestions t2 " \
          f"ON t1.id = t2.id AND t1.customer_id = t2.customer_id " \
          f"WHERE t1.msku IN {orderSourceSkuData}"
    res = cursor.execute_sql_many(sql)
    # sliceData = filterFbaListingCondition(res, templateRule, queryFiled)
    return res

# 分表获取Listing数据
def getSubmeterListingData(cursor, id, batchData, queryFiled, isFirst=True):

    filed = ','.join(queryFiled)
    if isFirst:
        sql = f"SELECT source_id, msku, {filed} FROM " \
              f"t_amazon_listing_{id} WHERE id in {batchData}"
    else:
        sql = f"SELECT source_id, msku, {filed} FROM " \
              f"t_amazon_listing_{id} WHERE msku in {batchData}"
    res = cursor.execute_sql_many(sql)
    return res

def getFbaListingInventoryData(orderSourceId, cursor, batchData, queryFiled):

    filed = ','.join(queryFiled)
    sql = f"SELECT t.source_id,t.msku, t.{filed}, t.last_update_time " \
          f"FROM t_amz_fba_virtual_inventory t " \
          f"INNER JOIN ( SELECT source_id, msku, max(last_update_time) AS max_last_update_time " \
          f"FROM t_amz_fba_virtual_inventory " \
          f"WHERE source_id = {orderSourceId} AND msku IN {batchData} GROUP BY source_id, msku ) t1 " \
          f"ON t.source_id = t1.source_id AND t.msku = t1.msku AND t.last_update_time = t1.max_last_update_time"
    res = cursor.execute_sql_many(sql)
    return res

def getSliceData(id, cursor, data, filedType):
    filed = ','.join(filedType)
    sql = f"SELECT source_id, msku, asin, {filed} FROM " \
          f"t_amazon_listing_{id} WHERE id IN {data}"
    res = cursor.execute_sql_many(sql)
    return res

def getAggregateData(orderSourceId, cursorProduct, sliceData, filedType):
    filed = ','.join(filedType)
    sql = f"SELECT source_id, msku, {filed} FROM " \
          f"t_amazon_sales_aggregate WHERE customer_id = 1 AND source_id ={orderSourceId} AND msku IN {sliceData}"
    res = cursorProduct.execute_sql_many(sql)
    return res

def getSliceOrderData(id, cursor, data, filedType):
    filed = ','.join(filedType)
    if "time" in filed:
        sql = f"SELECT source_id, msku, asin, {filed} FROM " \
              f"t_amazon_listing_{id} WHERE id IN {data}"
        res = cursor.execute_sql_many(sql)
    else:
        sql = f"SELECT source_id, msku, asin FROM " \
              f"t_amazon_listing_{id} WHERE id IN {data}"
        res = cursor.execute_sql_many(sql)
    return res

def getSubmeterListingOrderData(cursor, id, orderSourceId, condition):

    filed = ','.join(condition)
    sql = f"SELECT source_id, msku, {filed} FROM " \
          f"t_amazon_listing_{id} WHERE customer_id = 1 AND is_deleted = 0 AND source_id = {orderSourceId}"
    res = cursor.execute_sql_many(sql)
    return res

def getListingOrderOtherData(orderCursor, dateBase, customParam, sliceData):
    sliceData = list(filter(lambda x: x["sku"] != '' , sliceData))
    tempSliceData = tuple([i["sku"] for i in sliceData])
    if dateBase == "rb_first_order_patform_country" or dateBase == "rb_first_order_country":
        country = "US"
        sql = f"SELECT sku, country, " \
              f"DATE_FORMAT(MIN(first_order_time), '%Y-%m-%d')AS first_order_time, " \
              f"DATE_FORMAT(DATE_ADD(MIN(first_order_time), INTERVAL {customParam} DAY),'%Y-%m-%d') AS first_order_time_end " \
              f"FROM {dateBase} WHERE sku IN {tempSliceData} AND customer_id = 1 AND is_delete = 0 AND country = '{country}' " \
              f"GROUP BY sku"
        res = orderCursor.execute_sql_many(sql)
        lis = []
        for i in res:
            templis = list(filter(lambda x: x["sku"] == i["sku"], sliceData))
            for x in templis:
                lis.append({"source_id": x["source_id"], "sku": x["sku"], "msku": x["msku"], "country": country,
                            "first_order_time": i["first_order_time"],
                            "first_order_time_end": i["first_order_time_end"]})
    else:
        country = "US"
        sql = f"SELECT sku, " \
              f"DATE_FORMAT(MIN(first_order_time), '%Y-%m-%d')AS first_order_time, " \
              f"DATE_FORMAT(DATE_ADD(MIN(first_order_time), INTERVAL {customParam} DAY),'%Y-%m-%d') AS first_order_time_end " \
              f"FROM {dateBase} WHERE sku IN {tempSliceData} AND customer_id = 1 AND is_delete = 0 " \
              f"GROUP BY sku"
        res = orderCursor.execute_sql_many(sql)
        lis = []
        for i in res:
            templis = list(filter(lambda x: x["sku"] == i["sku"], sliceData))
            for x in templis:
                lis.append({"source_id":x["source_id"],"sku":x["sku"],"msku":x["msku"],"country":country,"first_order_time":i["first_order_time"],"first_order_time_end":i["first_order_time_end"]})
    return lis

def getTemplateOrderData(cursor, orderSourceId, sliceData, dateBase, minimum, maximum):
    if dateBase == "asin_first_order_time":
        dateBase = "asin_first_order_time"
    else:
        dateBase = "first_order_time"
    initSql = "SELECT sku, order_source_id AS source_id, order_source_sku AS msku, sum(order_num) AS sum FROM rb_report_order_source_sku_day WHERE "
    num = len(sliceData)
    sum = 1
    for item in sliceData:
        orderSourceSku = item["msku"]
        starTime = item[dateBase]
        endTime = item[dateBase + "_end"]
        if sum < num:
            tempFlied = f"(order_source_sku = '{orderSourceSku}' AND sales_day >= '{starTime}' AND sales_day <= '{endTime}') OR "
        else:
            tempFlied = f"(order_source_sku = '{orderSourceSku}' AND sales_day >= '{starTime}' AND sales_day <= '{endTime}') "
        initSql += tempFlied
        sum += 1
    sql =  f"AND order_source_id = {orderSourceId} GROUP BY order_source_id, order_source_sku " \
           f"HAVING sum >= {minimum} AND sum <= {maximum}"
    sql = initSql + sql
    res = cursor.execute_sql_many(sql)
    return res

def getListingOrderData(cursorProduct, id, batchData, dateBase, customParam, isFirst):
    """

    @param cursor:
    @param id:
    @param orderSourceId:
    @return:
    """
    if isFirst:
        sql = f"SELECT source_id, msku, DATE_FORMAT({dateBase}, '%Y-%m-%d') AS {dateBase}, " \
              f"DATE_FORMAT(DATE_ADD({dateBase}, INTERVAL {customParam} DAY),'%Y-%m-%d') AS {dateBase}_end " \
              f"FROM t_amazon_listing_{id} WHERE id IN {batchData}"
    else:
        batchData = tuple([i["msku"] for i in batchData])
        sql = f"SELECT source_id, msku, DATE_FORMAT({dateBase}, '%Y-%m-%d')AS {dateBase}, " \
              f"DATE_FORMAT(DATE_ADD({dateBase}, INTERVAL {customParam} DAY),'%Y-%m-%d') AS {dateBase}_end " \
              f"FROM t_amazon_listing_{id} WHERE msku IN {batchData}"
    res = cursorProduct.execute_sql_many(sql)
    sliceData = list(filter(lambda x: x[dateBase] != None, res))
    return sliceData
